﻿using System;
using System.Data;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Collections.Generic;

namespace Baubixinh_client
{
    public static class clsDbAccess
    {
        #region Entity

        public struct stNhapHang
        {
            public int intNhaphang_id;
            public DateTime dtNhaphang_date;
            public int intNhaphang_nguoinhap;
            public int intNhaphang_soluong;
            public string strNhaphang_ghichu;
        }

        public struct stNhapHangChiTiet
        {
            public int intNhaphang_id;
            public int intProduct_id;
            public int intKho_id;
            public int intMau_id;
            public int intSize_id;
            public int intSo_luong;
            public int intDon_gia;
            public int intKhoiluong;
        }

        public struct stDonHang
        {
            public int intDon_hang_id;
            public int intKho_hang_id;
            public int intLoai_khach;
            public string strTen_khach;
            public string strSdt;
            public string strEmail;
            public int intnguoi_ban1;
            public int intnguoi_ban2;
            public int intVan_chuyen;
            public int intTong_cong;
            public string strGhi_chu;
            public DateTime dtNgay_ban;
        }

        public struct stDonHangChiTiet
        {
            public int intDon_hang_id;
            public int intProduct_id;
            public string strProduct_name;
            public int intMau_id;
            public string strMu_name;
            public int intSize_id;
            public string strSize_name;
            public int intSo_luong;
            public int intDon_gia;
            public int intKhuyen_mai;
            public int intGia_goc;
        }

        public struct stProduct
        {
            public int intProID;
            public string strName;
            public string strEAN;
            public int intSo_luong;
            public int intNhaSX;
            public string strDescription;
        }

        public struct stExportFilter
        {
            public int intStoreID;
            public int intStaffID;
            public DateTime dtDateFrom;
            public DateTime dtDateTo;
        }

        public struct stSoLuong
        {
            public int intProductId;
            public int intKhoId;
            public int intMauId;
            public int intSizeId;
            public int intSoluong;
            public int intKhoiluong;
        }

        public struct stGiaBan
        {
            public int intProductID;
            public int intGiaGoc;
            public int intGiaLe;
            public int intGiaCap1;
            public int intGiaCap2;
            public int intGiaCap3;
            public int intKhuyenMai;
        }

        #endregion

        #region Nhap Hang

        public static DataTable fncGetDanhSachSP(Database objDb)
        {
            try
            {
                string strSQL = "";
                strSQL += " SELECT";
                strSQL += " `product_id`";
                strSQL += ",`product_ean`";
                strSQL += ",`product_thumb_image`";
                strSQL += ",`name_vi-VN`";
                strSQL += " FROM";
                strSQL += " `joomla_jshopping_products`";

                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                using (IDataReader dataReader = objDb.ExecuteReader(objCmnd))
                {
                    if (dataReader.FieldCount > 0)
                    {
                        DataTable dt = new DataTable();
                        dt.Load(dataReader);
                        if (dt.Rows.Count <= 0) return null;
                        return dt;
                    }
                    else
                    {
                        return null;
                    }
                }
            }
            catch { return null; }
        }

        public static int fncGetMaxNhapHang(Database objDb)
        {
            try
            {
                string strSQL = "";
                strSQL += " SELECT Max(`nhaphang_id`) from `joomla_jshopping_nhaphang`";

                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                using (IDataReader dataReader = objDb.ExecuteReader(objCmnd))
                {
                    if (dataReader.FieldCount > 0)
                    {
                        DataTable dt = new DataTable();
                        dt.Load(dataReader);
                        if (dt.Rows.Count <= 0) return 0;

                        return clsCommon.fncCnvNullToInt(dt.Rows[0][0]);
                    }
                    else
                    {
                        return 0;
                    }
                }
            }
            catch { return 0; }
        }

        public static bool fncInsertNhapHang(Database objDb, DbTransaction objTrans, stNhapHang stData)
        {
            try
            {
                string strSQL = "";
                strSQL += " INSERT INTO `joomla_jshopping_nhaphang`";
                strSQL += " SET";
                strSQL += " `nhaphang_id` = '" + stData.intNhaphang_id + "'";
                strSQL += ",`nhaphang_date` = '" + clsCommon.fncCnvToDbDatetime(stData.dtNhaphang_date) + "'";
                strSQL += ",`nhaphang_nguoinhap` = '" + stData.intNhaphang_nguoinhap + "'";
                strSQL += ",`nhaphang_soluong` = '" + stData.intNhaphang_soluong + "'";
                strSQL += ",`nhaphang_ghichu` = '" + clsCommon.fncCnvToDbString(stData.strNhaphang_ghichu) + "'";

                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                int intResult = objDb.ExecuteNonQuery(objCmnd, objTrans);
                if (intResult > 0) return true;
                else return false;
            }
            catch { return false; }
        }

        public static bool fncInsertNhapHangChiTiet(Database objDb, DbTransaction objTrans, stNhapHangChiTiet stData)
        {
            try
            {
                string strSQL = "";
                strSQL += " INSERT INTO `joomla_jshopping_nhaphang_chitiet2`";
                strSQL += " SET";
                strSQL += " `nhaphang_id` = '" + stData.intNhaphang_id + "'";
                strSQL += ",`product_id` = '" + stData.intProduct_id + "'";
                strSQL += ",`kho_id` = '" + stData.intKho_id + "'";
                strSQL += ",`mau_id` = '" + stData.intMau_id + "'";
                strSQL += ",`size_id` = '" + stData.intSize_id + "'";
                strSQL += ",`soluong` = '" + stData.intSo_luong + "'";
                strSQL += ",`don_gia` = '" + stData.intDon_gia + "'";
                strSQL += ",`khoiluong` = '" + stData.intKhoiluong + "'";

                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                int intResult = objDb.ExecuteNonQuery(objCmnd, objTrans);
                if (intResult > 0) return true;
                else return false;
            }
            catch { return false; }
        }

        public static bool fncUpdateIncreaseProductQuantity(Database objDb, DbTransaction objTrans, int intProID, int intQty)
        {
            try
            {
                string strSQL = "";
                strSQL += " UPDATE";
                strSQL += " `joomla_jshopping_products`";
                strSQL += " SET";
                strSQL += " `product_quantity` = `product_quantity` + '" + intQty + "'";
                strSQL += ",unlimited = 0";
                strSQL += " WHERE `product_id` = '" + intProID + "'";

                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                int intResult = objDb.ExecuteNonQuery(objCmnd, objTrans);
                if (intResult > 0) return true;
                else return false;
            }
            catch { return false; }
        }

        public static bool fncUpdateProductQuantity(Database objDb, DbTransaction objTrans, int intProID, int intQty)
        {
            try
            {
                string strSQL = "";
                strSQL += " UPDATE";
                strSQL += " `joomla_jshopping_products`";
                strSQL += " SET";
                strSQL += " `product_quantity` = '" + intQty + "'";
                strSQL += ",unlimited = 0";
                strSQL += " WHERE `product_id` = '" + intProID + "'";

                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                int intResult = objDb.ExecuteNonQuery(objCmnd, objTrans);
                if (intResult > 0) return true;
                else return false;
            }
            catch { return false; }
        }

        public static bool fncUpdateIncreaseProductQuantity2(Database objDb, DbTransaction objTrans, stNhapHangChiTiet stData)
        {
            try
            {
                string strSQL = "";

                strSQL += " INSERT INTO `joomla_jshopping_soluong`";
                strSQL += " SET ";
                strSQL += " `product_id` = '" + stData.intProduct_id + "'";
                strSQL += ",`kho_id` = '" + stData.intKho_id + "'";
                strSQL += ",`mau_id` = '" + stData.intMau_id + "'";
                strSQL += ",`size_id` = '" + stData.intSize_id + "'";
                strSQL += ",`soluong` = '" + stData.intSo_luong + "'";

                strSQL += " ON DUPLICATE KEY UPDATE";
                //strSQL += " SET";
                strSQL += " `soluong` = `soluong` + '" + stData.intSo_luong + "'";
                //strSQL += " WHERE";
                //strSQL += " `product_id` = '" + stData.intProduct_id + "'";
                //strSQL += " And `kho_id` = '" + stData.intKho_id + "'";
                //strSQL += " And `mau_id` = '" + stData.intMau_id + "'";
                //strSQL += " And `size_id` = '" + stData.intSize_id + "'";

                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                int intResult = objDb.ExecuteNonQuery(objCmnd, objTrans);
                if (intResult > 0) return true;
                else return false;
            }
            catch { return false; }
        }

        public static bool fncUpdateGiaBan(Database objDb, DbTransaction objTrans, int intProID, int intNewQty, int intNewGiaGoc, int intGiaLe, int intGiaCap1, int intGiaCap2, int intGiaCap3)
        {
            try
            {
                int intOldQty;

                string strSQL = "";

                //doc quantity trc do.
                strSQL = "SELECT `product_quantity` FROM `joomla_jshopping_products` WHERE `product_id` = '" + intProID + "'";
                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                using (IDataReader dataReader = objDb.ExecuteReader(objCmnd))
                {
                    if (dataReader.FieldCount > 0)
                    {
                        DataTable dt = new DataTable();
                        dt.Load(dataReader);
                        if (dt.Rows.Count <= 0) { intOldQty = 0; }
                        else { intOldQty = clsCommon.fncCnvNullToInt(dt.Rows[0][0]); }
                    }
                    else { intOldQty = 0; }
                }


                strSQL = "";
                strSQL += " UPDATE";
                strSQL += " `joomla_jshopping_giaban`";
                strSQL += " SET";
                strSQL += " `gia_goc`= " + intNewGiaGoc;
                //if (intNewQty >= 1 && intOldQty > 1)
                //{
                //    strSQL += " `gia_goc`= ((" + intOldQty + " * `gia_goc`) + (" + intNewQty + " * " + intNewGiaGoc + "))/(" + intOldQty + " * " + intNewQty + ")"; //doan nay chua lam dc
                //}
                //else
                //{
                //    if (intOldQty <= 1 && intNewQty >= 1) { strSQL += " `gia_goc`= " + intNewGiaGoc; }
                //    else { strSQL += " `gia_goc`= `gia_goc`"; }
                //}
                strSQL += ",`gia_ban_le`= " + intGiaLe;
                strSQL += ",`gia_cap1`= " + intGiaCap1;
                strSQL += ",`gia_cap2`= " + intGiaCap2;
                strSQL += ",`gia_cap3`= " + intGiaCap3;

                //strSQL += " FROM";
                //strSQL += " `joomla_jshopping_giaban` LEFT JOIN `joomla_jshopping_products`";
                //strSQL += " ON `joomla_jshopping_giaban`.`product_id` = `joomla_jshopping_products`.`product_id`";

                strSQL += " WHERE `product_id` = '" + intProID + "'";

                objCmnd = objDb.GetSqlStringCommand(strSQL);
                int intResult = objDb.ExecuteNonQuery(objCmnd, objTrans);
                if (intResult > 0) return true;
                else
                {
                    strSQL = "";
                    strSQL += " INSERT INTO";
                    strSQL += " `joomla_jshopping_giaban`";
                    strSQL += " SET";
                    strSQL += " `gia_goc`= " + intNewGiaGoc;
                    strSQL += ",`gia_ban_le`= " + intGiaLe;
                    strSQL += ",`gia_cap1`= " + intGiaCap1;
                    strSQL += ",`gia_cap2`= " + intGiaCap2;
                    strSQL += ",`gia_cap3`= " + intGiaCap3;
                    strSQL += ",`product_id` = '" + intProID + "'";
                    objCmnd = objDb.GetSqlStringCommand(strSQL);
                    intResult = objDb.ExecuteNonQuery(objCmnd, objTrans);
                    if (intResult > 0) return true;
                    else return false;
                }
            }
            catch { return false; }
        }

        public static bool fncUpdateGiaBan2(Database objDb, DbTransaction objTrans, int intProID, int intGiaGoc, int intGiaLe, int intGiaCap1, int intGiaCap2, int intGiaCap3)
        {
            try
            {
                string strSQL = "";

                strSQL += " INSERT INTO `joomla_jshopping_giaban`";
                strSQL += " SET ";
                strSQL += " `product_id` = '" + intProID + "'";
                strSQL += ",`gia_goc` = '" + intGiaGoc + "'";
                strSQL += ",`gia_ban_le` = '" + intGiaLe + "'";
                strSQL += ",`gia_cap1` = '" + intGiaCap1 + "'";
                strSQL += ",`gia_cap2` = '" + intGiaCap2 + "'";
                strSQL += ",`gia_cap3` = '" + intGiaCap3 + "'";

                strSQL += " ON DUPLICATE KEY UPDATE";
                //strSQL += " SET";
                strSQL += " `gia_goc` = '" + intGiaGoc + "'";
                strSQL += ",`gia_ban_le` = '" + intGiaLe + "'";
                strSQL += ",`gia_cap1` = '" + intGiaCap1 + "'";
                strSQL += ",`gia_cap2` = '" + intGiaCap2 + "'";
                strSQL += ",`gia_cap3` = '" + intGiaCap3 + "'";
                //strSQL += " WHERE";
                //strSQL += " `product_id` = '" + intProID;

                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                int intResult = objDb.ExecuteNonQuery(objCmnd, objTrans);
                if (intResult > 0) return true;
                else return false;
            }
            catch { return false; }
        }

        public static bool fncUpdateGiaBan3(Database objDb, DbTransaction objTrans, stGiaBan stData)
        {
            //khac so voi update giaban3 la them phan khuyen mai

            try
            {
                string strSQL = "";

                strSQL += " INSERT INTO `joomla_jshopping_giaban`";
                strSQL += " SET ";
                strSQL += " `product_id` = '" + stData.intProductID + "'";
                strSQL += ",`gia_goc` = '" + stData.intGiaGoc + "'";
                strSQL += ",`gia_ban_le` = '" + stData.intGiaLe + "'";
                strSQL += ",`gia_cap1` = '" + stData.intGiaCap1 + "'";
                strSQL += ",`gia_cap2` = '" + stData.intGiaCap2 + "'";
                strSQL += ",`gia_cap3` = '" + stData.intGiaCap3 + "'";
                strSQL += ",`khuyen_mai` = '" + stData.intKhuyenMai + "'";

                strSQL += " ON DUPLICATE KEY UPDATE";
                strSQL += " `gia_goc` = '" + stData.intGiaGoc + "'";
                strSQL += ",`gia_ban_le` = '" + stData.intGiaLe + "'";
                strSQL += ",`gia_cap1` = '" + stData.intGiaCap1 + "'";
                strSQL += ",`gia_cap2` = '" + stData.intGiaCap2 + "'";
                strSQL += ",`gia_cap3` = '" + stData.intGiaCap3 + "'";
                strSQL += ",`khuyen_mai` = '" + stData.intKhuyenMai + "'";

                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                int intResult = objDb.ExecuteNonQuery(objCmnd, objTrans);
                if (intResult > 0) return true;
                else return false;
            }
            catch { return false; }
        }

        public static bool fncUpdateSoLuongSanPham(Database objDb, DbTransaction objTrans, int intProID, int intKhoID, int intMauID, int intSizeID, int intSoluong)
        {
            try
            {
                string strSQL = "";
                //strSQL += " IF EXISTS (SELECT * FROM `joomla_jshopping_soluong`";
                //strSQL += " WHERE ";
                //strSQL += " `product_id` = " + intProID;
                //strSQL += " AND `kho_id`= " + intKhoID;
                //strSQL += " AND `mau_id` =" + intMauID;
                //strSQL += " AND `size_id` =" + intSizeID;
                //strSQL += " )";

                //strSQL += " UPDATE `joomla_jshopping_soluong`";
                //strSQL += " SET `soluong` = `soluong` + " + intSoluong;
                //strSQL += " WHERE ";
                //strSQL += " `product_id` = " + intProID;
                //strSQL += " AND `kho_id`= " + intKhoID;
                //strSQL += " AND `mau_id` =" + intMauID;
                //strSQL += " AND `size_id` =" + intSizeID;

                //strSQL += " ELSE";
                strSQL += " INSERT INTO `joomla_jshopping_soluong`";
                strSQL += " SET ";
                strSQL += " `product_id` = " + intProID;
                strSQL += ",`kho_id` = " + intKhoID;
                strSQL += ",`mau_id` = " + intMauID;
                strSQL += ",`size_id` = " + intSizeID;
                strSQL += ",`soluong` = " + intSoluong;

                strSQL += " ON DUPLICATE KEY UPDATE";
                strSQL += " `soluong` = `soluong` +" + intSoluong;

                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                int intResult = objDb.ExecuteNonQuery(objCmnd, objTrans);
                if (intResult > 0) return true;
                else return false;
            }
            catch { return false; }
        }

        public static bool fncUpdateSoLuongTuDb(Database objDb, DbTransaction objTrans)
        {
            try
            {

                string strSQL = "UPDATE joomla_jshopping_products Set `product_quantity` = 0";

                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                int intResult = objDb.ExecuteNonQuery(objCmnd, objTrans);

                strSQL += " UPDATE joomla_jshopping_products c";
                strSQL += " INNER JOIN (";
                strSQL += " SELECT ";
                strSQL += " `product_id`";
                strSQL += " , SUM(`soluong`) As `total`";
                strSQL += " FROM `joomla_jshopping_soluong` ";
                strSQL += " GROUP BY";
                strSQL += " `product_id` ";
                strSQL += " ) x ON c.product_id= x.product_id";
                strSQL += " SET c.product_quantity= x.total";

                objCmnd = objDb.GetSqlStringCommand(strSQL);
                intResult = objDb.ExecuteNonQuery(objCmnd, objTrans);

                if (intResult > 0) return true;
                else return false;
            }
            catch { return false; }
        }

        public static DataTable fncGetNhapHang(Database objDb, int intNhapHangID = -1)
        {
            try
            {
                string strSQL = "";
                strSQL += " SELECT";
                strSQL += " `nhaphang_id`";
                strSQL += ",`nhaphang_date`";
                strSQL += ",`nhaphang_nguoinhap`";
                strSQL += ",`tennhanvien`";
                strSQL += ",`nhaphang_soluong`";
                strSQL += ",`nhaphang_ghichu`";
                strSQL += " FROM";
                strSQL += " `joomla_jshopping_nhaphang`";
                strSQL += " Left Join (";
                strSQL += " SELECT `value_id`, `name_vi-VN` as `tennhanvien`, `value_ordering`";
                strSQL += " FROM `joomla_jshopping_attr_values`";
                strSQL += " ORDER BY `value_ordering` ) as nhanvien on nhanvien.value_id = joomla_jshopping_nhaphang.nhaphang_nguoinhap";

                if (intNhapHangID > -1) strSQL += " WHERE `nhaphang_id` = '" + intNhapHangID + "'";

                strSQL += " ORDER BY nhaphang_date DESC, nhaphang_id DESC";

                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                using (IDataReader dataReader = objDb.ExecuteReader(objCmnd))
                {
                    if (dataReader.FieldCount > 0)
                    {
                        DataTable dt = new DataTable();
                        dt.Load(dataReader);
                        if (dt.Rows.Count <= 0) return null;
                        return dt;
                    }
                    else
                    {
                        return null;
                    }
                }
            }
            catch { return null; }
        }

        public static DataTable fncGetNhapHangChiTiet(Database objDb, int intNhapHangID)
        {
            try
            {
                string strSQL = "UPDATE joomla_jshopping_giaban INNER JOIN joomla_jshopping_products ON joomla_jshopping_giaban.`product_id` = joomla_jshopping_products.`product_id` SET joomla_jshopping_giaban.`gia_ban_le` = joomla_jshopping_products.product_price;";
                //strSQL += " SELECT";
                //strSQL += " `nhaphang_id`";
                //strSQL += ",`joomla_jshopping_nhaphang_chitiet`.`product_id`";
                //strSQL += ",pro.`product_name`";
                //strSQL += ",pro.`product_ean`";
                //strSQL += ",`so_luong`";
                //strSQL += ",`don_gia`";
                //strSQL += ",0 as total";
                //strSQL += " FROM `joomla_jshopping_nhaphang_chitiet`";
                //strSQL += " Left Join(Select `product_id`, `product_ean`, `name_vi-VN` as product_name FROM `joomla_jshopping_products`) As pro On pro.`product_id` =  `joomla_jshopping_nhaphang_chitiet`.`product_id`";

                //strSQL += " WHERE `nhaphang_id` = '" + intNhapHangID + "'";

                strSQL += " SELECT";
                strSQL += " nhaphang.`nhaphang_id`";
                strSQL += ",nhaphang.`product_id`";
                strSQL += ",sanpham.tensanpham";
                strSQL += ",sanpham.product_ean";
                strSQL += ",nhaphang.`kho_id`";
                strSQL += ",nhaphang.`mau_id`";
                strSQL += ",mausac.tenmau";
                strSQL += ",nhaphang.`size_id`";
                strSQL += ",kichthuoc.tensize";
                strSQL += ",nhaphang.`soluong`";
                strSQL += ",nhaphang.`don_gia`";
                strSQL += ",nhaphang.`khoiluong` ";
                strSQL += ",0 as total";

                strSQL += " FROM";
                strSQL += "`joomla_jshopping_nhaphang_chitiet2` as nhaphang";

                strSQL += " Left Join (";
                strSQL += " SELECT `value_id`, `attr_id`, `name_vi-VN` as `tenmau`, `value_ordering`";
                strSQL += " FROM `joomla_jshopping_attr_values`";
                strSQL += " ORDER BY `value_ordering` ) as mausac on mausac.value_id = nhaphang.mau_id";

                strSQL += " Left Join (";
                strSQL += " SELECT `value_id`, `attr_id`, `name_vi-VN` as `tensize`, `value_ordering`";
                strSQL += " FROM `joomla_jshopping_attr_values`";
                strSQL += " ORDER BY `value_ordering` ) as kichthuoc on kichthuoc.value_id = nhaphang.size_id";

                strSQL += " Left Join (";
                strSQL += " SELECT product_id, `name_vi-VN` as `tensanpham`, `product_ean`";
                strSQL += " FROM `joomla_jshopping_products`";
                strSQL += " ) as sanpham on sanpham.product_id = nhaphang.product_id";

                strSQL += " WHERE `nhaphang_id` = '" + intNhapHangID + "'";

                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                using (IDataReader dataReader = objDb.ExecuteReader(objCmnd))
                {
                    if (dataReader.FieldCount > 0)
                    {
                        DataTable dt = new DataTable();
                        dt.Load(dataReader);
                        if (dt.Rows.Count <= 0) return null;
                        return dt;
                    }
                    else
                    {
                        return null;
                    }
                }
            }
            catch { return null; }
        }

        public static DataTable fncGetNhapHangChiTietByProduct(Database objDb, int intProductID)
        {
            try
            {
                string strSQL = "";
                strSQL += " SELECT";
                strSQL += " chitiet.`nhaphang_id`";
                strSQL += ",chitiet.`product_id`";
                strSQL += ",chitiet.`soluong`";
                strSQL += ",chitiet.`don_gia`";
                strSQL += ",nhaphang.`nhaphang_date`";

                strSQL += " FROM `joomla_jshopping_nhaphang_chitiet2` as chitiet";
                strSQL += " Left Join `joomla_jshopping_nhaphang` As nhaphang On nhaphang.`nhaphang_id` =  chitiet.`nhaphang_id`";

                strSQL += " WHERE `product_id` = '" + intProductID + "'";

                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                using (IDataReader dataReader = objDb.ExecuteReader(objCmnd))
                {
                    if (dataReader.FieldCount > 0)
                    {
                        DataTable dt = new DataTable();
                        dt.Load(dataReader);
                        if (dt.Rows.Count <= 0) return null;
                        return dt;
                    }
                    else
                    {
                        return null;
                    }
                }
            }
            catch { return null; }
        }

        #endregion

        #region Ban Hang

        public static DataTable fncGetColorByProduct(Database objDb, int intKhoID, int intProductID)
        {
            try
            {
                string strSQL = "";
                strSQL += " SELECT DISTINCT";
                strSQL += " soluong.`mau_id` as value";
                strSQL += ",attr.`name_vi-VN` as name";

                strSQL += " FROM `joomla_jshopping_soluong` as soluong";

                strSQL += " LEFT JOIN `joomla_jshopping_attr_values` as attr";
                strSQL += " ON soluong.`mau_id` = attr.`value_id`";

                strSQL += " WHERE";
                strSQL += " soluong.`kho_id` = " + intKhoID;
                strSQL += " AND soluong.`product_id` = " + intProductID;

                strSQL += " ORDER BY attr.`value_ordering`";

                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                using (IDataReader dataReader = objDb.ExecuteReader(objCmnd))
                {
                    if (dataReader.FieldCount > 0)
                    {
                        DataTable dt = new DataTable();
                        dt.Load(dataReader);
                        if (dt.Rows.Count <= 0) return null;
                        return dt;
                    }
                    else
                    {
                        return null;
                    }
                }
            }
            catch { return null; }
        }

        public static DataTable fncGetSizeByProduct(Database objDb, int intKhoID, int intProductID, int intMauID)
        {
            try
            {
                string strSQL = "";
                strSQL += " SELECT DISTINCT";
                strSQL += " soluong.`size_id` as value";
                strSQL += ",attr.`name_vi-VN` as name";

                strSQL += " FROM `joomla_jshopping_soluong` as soluong";

                strSQL += " LEFT JOIN `joomla_jshopping_attr_values` as attr";
                strSQL += " ON soluong.`size_id` = attr.`value_id`";

                strSQL += " WHERE";
                strSQL += " soluong.`kho_id` = " + intKhoID;
                strSQL += " AND soluong.`product_id` = " + intProductID;
                strSQL += " AND soluong.`mau_id` = " + intMauID;

                strSQL += " ORDER BY attr.`value_ordering`";

                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                using (IDataReader dataReader = objDb.ExecuteReader(objCmnd))
                {
                    if (dataReader.FieldCount > 0)
                    {
                        DataTable dt = new DataTable();
                        dt.Load(dataReader);
                        if (dt.Rows.Count <= 0) return null;
                        return dt;
                    }
                    else
                    {
                        return null;
                    }
                }
            }
            catch { return null; }
        }

        public static int fncGetQuantityByProduct(Database objDb, int intKhoID, int intProductID, int intMauID, int intSizeID)
        {
            try
            {
                string strSQL = "";
                strSQL += " SELECT DISTINCT";
                strSQL += " soluong.`soluong` as soluong";

                strSQL += " FROM `joomla_jshopping_soluong` as soluong";

                strSQL += " WHERE";
                strSQL += " soluong.`kho_id` = " + intKhoID;
                strSQL += " AND soluong.`product_id` = " + intProductID;
                strSQL += " AND soluong.`mau_id` = " + intMauID;
                strSQL += " AND soluong.`size_id` = " + intSizeID;

                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                using (IDataReader dataReader = objDb.ExecuteReader(objCmnd))
                {
                    if (dataReader.FieldCount > 0)
                    {
                        DataTable dt = new DataTable();
                        dt.Load(dataReader);
                        if (dt.Rows.Count <= 0) return 0;

                        //tra ve so luong
                        return clsCommon.fncCnvNullToInt(dt.Rows[0][0]);
                    }
                    else
                    {
                        return 0;
                    }
                }
            }
            catch { return 0; }
        }

        public static int fncGetMaxDonHang(Database objDb)
        {
            try
            {
                string strSQL = "";
                strSQL += " SELECT Max(`don_hang_id`) from `joomla_jshopping_donhang`";

                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                using (IDataReader dataReader = objDb.ExecuteReader(objCmnd))
                {
                    if (dataReader.FieldCount > 0)
                    {
                        DataTable dt = new DataTable();
                        dt.Load(dataReader);
                        if (dt.Rows.Count <= 0) return 0;

                        return clsCommon.fncCnvNullToInt(dt.Rows[0][0]);
                    }
                    else
                    {
                        return 0;
                    }
                }
            }
            catch { return 0; }
        }

        public static bool fncInsertDonHang(Database objDb, DbTransaction objTrans, stDonHang stData)
        {
            try
            {
                string strSQL = "";
                strSQL += " INSERT INTO `joomla_jshopping_donhang`";
                strSQL += " SET";

                strSQL += " `don_hang_id` = " + stData.intDon_hang_id;
                strSQL += ",`kho_hang_id` = " + stData.intKho_hang_id;
                strSQL += ",`loai_khach` = " + stData.intLoai_khach;
                strSQL += ",`ten_khach` = '" + clsCommon.fncCnvToDbString(stData.strTen_khach) + "'";
                strSQL += ",`sdt` = '" + clsCommon.fncCnvToDbString(stData.strSdt) + "'";
                strSQL += ",`email` = '" + clsCommon.fncCnvToDbString(stData.strEmail) + "'";
                strSQL += ",`nguoi_ban1` = " + stData.intnguoi_ban1;
                strSQL += ",`nguoi_ban2` = " + stData.intnguoi_ban2;
                strSQL += ",`van_chuyen` = " + stData.intVan_chuyen;
                strSQL += ",`tong_cong` = " + stData.intTong_cong;
                strSQL += ",`ghi_chu` = '" + clsCommon.fncCnvToDbString(stData.strGhi_chu) + "'";
                strSQL += ",`ngay_ban` = '" + clsCommon.fncCnvToDbDatetime(stData.dtNgay_ban) + "'";

                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                int intResult = objDb.ExecuteNonQuery(objCmnd, objTrans);
                if (intResult > 0) return true;
                else return false;
            }
            catch { return false; }
        }

        public static bool fncInsertDonHangChiTiet(Database objDb, DbTransaction objTrans, stDonHangChiTiet stData)
        {
            try
            {
                string strSQL = "";
                strSQL += " INSERT INTO `joomla_jshopping_donhang_chitiet`";
                strSQL += " SET";

                strSQL += " `don_hang_id` = " + stData.intDon_hang_id;
                strSQL += ",`product_id` = " + stData.intProduct_id;
                strSQL += ",`product_name` = '" + clsCommon.fncCnvToDbString(stData.strProduct_name) + "'";
                strSQL += ",`mau_id` = " + stData.intMau_id;
                strSQL += ",`mau_name` = '" + clsCommon.fncCnvToDbString(stData.strMu_name) + "'";
                strSQL += ",`size_id` = " + stData.intSize_id;
                strSQL += ",`size_name` = '" + clsCommon.fncCnvToDbString(stData.strSize_name) + "'";
                strSQL += ",`so_luong` = " + stData.intSo_luong;
                strSQL += ",`don_gia` = " + stData.intDon_gia;
                strSQL += ",`khuyen_mai` = " + stData.intKhuyen_mai;
                strSQL += ",`gia_goc` = " + stData.intGia_goc;

                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                int intResult = objDb.ExecuteNonQuery(objCmnd, objTrans);
                if (intResult > 0) return true;
                else return false;
            }
            catch { return false; }
        }

        public static bool fncInsertDonHangTraLai(Database objDb, DbTransaction objTrans, stDonHangChiTiet stData)
        {
            try
            {
                string strSQL = "";
                strSQL += " INSERT INTO `joomla_jshopping_donhang_tralai`";
                strSQL += " SET";

                strSQL += " `don_hang_id` = " + stData.intDon_hang_id;
                strSQL += ",`product_id` = " + stData.intProduct_id;
                strSQL += ",`product_name` = '" + clsCommon.fncCnvToDbString(stData.strProduct_name) + "'";
                strSQL += ",`mau_id` = " + stData.intMau_id;
                strSQL += ",`mau_name` = '" + clsCommon.fncCnvToDbString(stData.strMu_name) + "'";
                strSQL += ",`size_id` = " + stData.intSize_id;
                strSQL += ",`size_name` = '" + clsCommon.fncCnvToDbString(stData.strSize_name) + "'";
                strSQL += ",`so_luong` = " + stData.intSo_luong;
                strSQL += ",`don_gia` = " + stData.intDon_gia;
                strSQL += ",`khuyen_mai` = " + stData.intKhuyen_mai;
                strSQL += ",`gia_goc` = " + stData.intGia_goc;

                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                int intResult = objDb.ExecuteNonQuery(objCmnd, objTrans);
                if (intResult > 0) return true;
                else return false;
            }
            catch { return false; }
        }

        public static bool fncUpdateDeductProductQuantity(Database objDb, DbTransaction objTrans, int intProID, int intQty)
        {
            try
            {
                string strSQL = "";
                strSQL += " UPDATE";
                strSQL += " `joomla_jshopping_products`";
                strSQL += " SET `product_quantity` = `product_quantity` - '" + intQty + "'";
                strSQL += " WHERE `product_id` = '" + intProID + "'";

                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                int intResult = objDb.ExecuteNonQuery(objCmnd, objTrans);
                if (intResult > 0) return true;
                else return false;
            }
            catch { return false; }
        }

        public static bool fncUpdateDeductProductQuantityDetail(Database objDb, DbTransaction objTrans, int intProID, int intKhoID, int intMauID, int intSizeID, int intQty)
        {
            try
            {
                string strSQL = "";

                int intCount;

                //doc quantity trc do.
                strSQL = "SELECT * FROM `joomla_jshopping_soluong` WHERE `product_id` = '" + intProID + "'";
                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                using (IDataReader dataReader = objDb.ExecuteReader(objCmnd))
                {
                    if (dataReader.FieldCount > 0)
                    {
                        DataTable dt = new DataTable();
                        dt.Load(dataReader);
                        if (dt.Rows.Count <= 0) { intCount = 0; }
                        else { intCount = clsCommon.fncCnvNullToInt(dt.Rows[0][0]); }
                    }
                    else { intCount = 0; }
                }

                if (intCount <= 0) return true;

                strSQL = "";
                strSQL += " UPDATE";
                strSQL += " `joomla_jshopping_soluong`";
                strSQL += " SET `soluong` = `soluong` - '" + intQty + "'";
                strSQL += " WHERE";
                strSQL += " `product_id` = '" + intProID + "'";
                strSQL += " AND `kho_id` = '" + intKhoID + "'";
                strSQL += " AND `mau_id`= '" + intMauID + "'";
                strSQL += " AND `size_id`= '" + intSizeID + "'";

                objCmnd = objDb.GetSqlStringCommand(strSQL);
                int intResult = objDb.ExecuteNonQuery(objCmnd, objTrans);
                if (intResult > 0) return true;
                else return false;
            }
            catch { return false; }
        }

        public static DataTable fncGetDonHang(Database objDb, stExportFilter stFilter)
        {
            try
            {
                string strSQL = "";
                strSQL += " SELECT";
                strSQL += " `don_hang_id`";
                strSQL += ",`kho_hang_id`";
                strSQL += ",tenkhohang";
                strSQL += ",`loai_khach`";
                strSQL += ",`ten_khach`";
                strSQL += ",`sdt`";
                strSQL += ",`email`";
                strSQL += ",`nguoi_ban1`";
                strSQL += ",`nguoi_ban1_ten`";
                strSQL += ",`nguoi_ban2`";
                strSQL += ",`nguoi_ban2_ten`";
                strSQL += ",`van_chuyen`";
                strSQL += ",`tong_cong`";
                strSQL += ",`ghi_chu`";
                strSQL += ",`ngay_ban` ";

                strSQL += " FROM";
                strSQL += " `joomla_jshopping_donhang`";

                strSQL += " Left Join (";
                strSQL += " SELECT `value_id`, `attr_id`, `name_vi-VN` as `tenkhohang`, `value_ordering`";
                strSQL += " FROM `joomla_jshopping_attr_values`";
                strSQL += " ORDER BY `value_ordering` ) as khohang on khohang.value_id = joomla_jshopping_donhang.kho_hang_id";

                strSQL += " Left Join (";
                strSQL += " SELECT `value_id`, `attr_id`, `name_vi-VN` as `nguoi_ban1_ten`, `value_ordering`";
                strSQL += " FROM `joomla_jshopping_attr_values`";
                strSQL += " ORDER BY `value_ordering` ) as nguoiban1 on nguoiban1.value_id = joomla_jshopping_donhang.nguoi_ban1";

                strSQL += " Left Join (";
                strSQL += " SELECT `value_id`, `attr_id`, `name_vi-VN` as `nguoi_ban2_ten`, `value_ordering`";
                strSQL += " FROM `joomla_jshopping_attr_values`";
                strSQL += " ORDER BY `value_ordering` ) as nguoiban2 on nguoiban2.value_id = joomla_jshopping_donhang.nguoi_ban2";

                strSQL += " WHERE";
                strSQL += " 1 = 1";

                if (stFilter.intStoreID > 0) strSQL += " AND `kho_hang_id` = " + stFilter.intStoreID;
                if (stFilter.intStaffID > 0) strSQL += " AND (`nguoi_ban1` = " + stFilter.intStaffID + " OR `nguoi_ban2` = " + stFilter.intStaffID + " )";
                if (stFilter.dtDateFrom > DateTime.MinValue) strSQL += " AND `ngay_ban` >= '" + clsCommon.fncCnvToDbDatetime(stFilter.dtDateFrom) + "'";
                if (stFilter.dtDateTo > DateTime.MinValue) strSQL += " AND `ngay_ban` <= '" + clsCommon.fncCnvToDbDatetime(stFilter.dtDateTo) + "'";

                strSQL += " ORDER BY ngay_ban DESC";

                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                using (IDataReader dataReader = objDb.ExecuteReader(objCmnd))
                {
                    if (dataReader.FieldCount > 0)
                    {
                        DataTable dt = new DataTable();
                        dt.Load(dataReader);
                        if (dt.Rows.Count <= 0) return null;
                        return dt;
                    }
                    else
                    {
                        return null;
                    }
                }
            }
            catch { return null; }
        }

        public static DataTable fncGetDonHangBaoCao(Database objDb, stExportFilter stFilter)
        {
            try
            {
                string strSQL = "";
                strSQL += " SELECT";
                strSQL += " `joomla_jshopping_donhang`.`don_hang_id` as 'ID đơn hàng'";
                //strSQL += ",`kho_hang_id`";
                strSQL += ",tenkhohang as 'Tên kho hàng'";
                strSQL += ",`loai_khach`";
                strSQL += ",`ten_khach` as 'Tên khách hàng'";
                strSQL += ",`sdt` as 'SĐT'";
                strSQL += ",`email` as 'Email'";
                //strSQL += ",`nguoi_ban1`";
                strSQL += ",`nguoi_ban1_ten` as 'Người bán 1'";
                //strSQL += ",`nguoi_ban2`";
                strSQL += ",`nguoi_ban2_ten` as 'Người bán 2'";
                strSQL += ",`van_chuyen` as 'Phí vận chuyển'";
                strSQL += ",`tong_cong` as 'Hóa đơn mua'";
                strSQL += ",trahang.`tra_lai` as 'Trả lại hàng'";
                strSQL += ",(`tong_cong` - trahang.`tra_lai`) as 'Doanh thu'";
                strSQL += ",`ghi_chu` as 'Ghi chú'";
                strSQL += ",`ngay_ban` as 'Ngày bán'";

                strSQL += " FROM";
                strSQL += " `joomla_jshopping_donhang`";

                strSQL += " Left Join (";
                strSQL += " SELECT `value_id`, `attr_id`, `name_vi-VN` as `tenkhohang`, `value_ordering`";
                strSQL += " FROM `joomla_jshopping_attr_values`";
                strSQL += " ORDER BY `value_ordering` ) as khohang on khohang.value_id = joomla_jshopping_donhang.kho_hang_id";

                strSQL += " Left Join (";
                strSQL += " SELECT `value_id`, `attr_id`, `name_vi-VN` as `nguoi_ban1_ten`, `value_ordering`";
                strSQL += " FROM `joomla_jshopping_attr_values`";
                strSQL += " ORDER BY `value_ordering` ) as nguoiban1 on nguoiban1.value_id = joomla_jshopping_donhang.nguoi_ban1";

                strSQL += " Left Join (";
                strSQL += " SELECT `value_id`, `attr_id`, `name_vi-VN` as `nguoi_ban2_ten`, `value_ordering`";
                strSQL += " FROM `joomla_jshopping_attr_values`";
                strSQL += " ORDER BY `value_ordering` ) as nguoiban2 on nguoiban2.value_id = joomla_jshopping_donhang.nguoi_ban2";

                strSQL += " Left Join (";
                strSQL += " SELECT `joomla_jshopping_donhang_tralai`.`don_hang_id`, SUM(`so_luong` * (`don_gia`- `khuyen_mai`)) as `tra_lai`";
                strSQL += " FROM `joomla_jshopping_donhang_tralai`";
                strSQL += " GROUP BY `joomla_jshopping_donhang_tralai`.`don_hang_id`) as trahang on trahang.don_hang_id = joomla_jshopping_donhang.don_hang_id";

                strSQL += " WHERE";
                strSQL += " 1 = 1";

                if (stFilter.intStoreID > 0) strSQL += " AND `kho_hang_id` = " + stFilter.intStoreID;
                if (stFilter.intStaffID > 0) strSQL += " AND (`nguoi_ban1` = " + stFilter.intStaffID + " OR `nguoi_ban2` = " + stFilter.intStaffID + " )";
                if (stFilter.dtDateFrom > DateTime.MinValue) strSQL += " AND `ngay_ban` >= '" + clsCommon.fncCnvToDbDatetime(stFilter.dtDateFrom) + "'";
                if (stFilter.dtDateTo > DateTime.MinValue) strSQL += " AND `ngay_ban` <= '" + clsCommon.fncCnvToDbDatetime(stFilter.dtDateTo) + "'";

                strSQL += " ORDER BY ngay_ban DESC";

                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                using (IDataReader dataReader = objDb.ExecuteReader(objCmnd))
                {
                    if (dataReader.FieldCount > 0)
                    {
                        DataTable dt = new DataTable();
                        dt.Load(dataReader);
                        if (dt.Rows.Count <= 0) return null;
                        return dt;
                    }
                    else
                    {
                        return null;
                    }
                }
            }
            catch { return null; }
        }

        public static DataTable fncGetDonHangChiTiet(Database objDb, int intDonHangID)
        {
            try
            {
                string strSQL = "";
                strSQL += " SELECT";
                strSQL += " chitiet.`don_hang_id`";
                strSQL += ",chitiet.`product_id`";
                strSQL += ",chitiet.`product_name`";
                strSQL += ",chitiet.`mau_id`";
                strSQL += ",chitiet.`mau_name`";
                strSQL += ",chitiet.`size_id`";
                strSQL += ",chitiet.`size_name`";
                strSQL += ",chitiet.`so_luong`";
                strSQL += ",chitiet.`don_gia`";
                strSQL += ",chitiet.`khuyen_mai`";
                strSQL += ",chitiet.`gia_goc` ";
                strSQL += ",(chitiet.`so_luong` * (chitiet.`don_gia` - chitiet.`khuyen_mai`)) as thanh_tien";
                strSQL += ",pro.`product_ean` ";
                strSQL += " FROM";
                strSQL += " `joomla_jshopping_donhang_chitiet` as chitiet";
                strSQL += " LEFT JOIN joomla_jshopping_products as pro";
                strSQL += " ON chitiet.`product_id` = pro.`product_id`";

                strSQL += " WHERE chitiet.`don_hang_id` = '" + intDonHangID + "'";
                strSQL += " ORDER BY chitiet.product_id, chitiet.mau_id, chitiet.size_id";


                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                using (IDataReader dataReader = objDb.ExecuteReader(objCmnd))
                {
                    if (dataReader.FieldCount > 0)
                    {
                        DataTable dt = new DataTable();
                        dt.Load(dataReader);
                        if (dt.Rows.Count <= 0) return null;
                        return dt;
                    }
                    else
                    {
                        return null;
                    }
                }
            }
            catch { return null; }
        }

        public static DataTable fncGetDonHangTraLai(Database objDb, int intDonHangID)
        {
            try
            {
                string strSQL = "";
                strSQL += " SELECT";
                strSQL += " tralai.`don_hang_id`";
                strSQL += ",tralai.`product_id`";
                strSQL += ",tralai.`product_name`";
                strSQL += ",tralai.`mau_id`";
                strSQL += ",tralai.`mau_name`";
                strSQL += ",tralai.`size_id`";
                strSQL += ",tralai.`size_name`";
                strSQL += ",tralai.`so_luong`";
                strSQL += ",tralai.`don_gia`";
                strSQL += ",tralai.`khuyen_mai`";
                strSQL += ",tralai.`gia_goc` ";
                strSQL += ",(tralai.`so_luong` * (tralai.`don_gia` - tralai.`khuyen_mai`)) as thanh_tien";
                strSQL += ",pro.`product_ean` ";
                strSQL += " FROM";
                strSQL += " `joomla_jshopping_donhang_tralai` as tralai";
                strSQL += " LEFT JOIN joomla_jshopping_products as pro";
                strSQL += " ON tralai.`product_id` = pro.`product_id`";

                strSQL += " WHERE tralai.`don_hang_id` = '" + intDonHangID + "'";
                strSQL += " ORDER BY tralai.product_id, tralai.mau_id, tralai.size_id";


                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                using (IDataReader dataReader = objDb.ExecuteReader(objCmnd))
                {
                    if (dataReader.FieldCount > 0)
                    {
                        DataTable dt = new DataTable();
                        dt.Load(dataReader);
                        if (dt.Rows.Count <= 0) return null;
                        return dt;
                    }
                    else
                    {
                        return null;
                    }
                }
            }
            catch { return null; }
        }

        #endregion

        #region So Luong

        public static bool fncDeleteQuantity(Database objDb, DbTransaction objTrans, int intProID)
        {
            try
            {
                string strSQL = "";
                strSQL += " DELETE FROM";
                strSQL += " `joomla_jshopping_soluong`";
                strSQL += " WHERE `product_id` = '" + intProID + "'";

                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                int intResult = objDb.ExecuteNonQuery(objCmnd, objTrans);
                if (intResult > 0) return true;
                else return false;
            }
            catch { return false; }
        }

        public static bool fncInsertQuantity(Database objDb, DbTransaction objTrans, stSoLuong stData)
        {
            try
            {
                string strSQL = "";
                strSQL += " INSERT INTO";
                strSQL += " `joomla_jshopping_soluong`";
                strSQL += " SET";
                strSQL += " `product_id` = " + stData.intProductId;
                strSQL += ",`kho_id` = " + stData.intKhoId;
                strSQL += ",`mau_id` = " + stData.intMauId;
                strSQL += ",`size_id` = " + stData.intSizeId;
                strSQL += ",`soluong` = " + stData.intSoluong;
                strSQL += ",`khoiluong` = " + stData.intKhoiluong;

                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                int intResult = objDb.ExecuteNonQuery(objCmnd, objTrans);
                if (intResult > 0) return true;
                else return false;
            }
            catch { return false; }
        }

        #endregion

        #region Dung Chung

        public static DataTable fncGetDanhMuc(Database objDb)
        {
            try
            {
                string strSQL = "";
                strSQL += " SELECT";
                strSQL += " `category_id`";
                strSQL += ",`category_parent_id`";
                strSQL += ",`ordering`";
                strSQL += ",`name_vi-VN`";

                strSQL += " FROM";
                strSQL += " `joomla_jshopping_categories`";

                strSQL += " ORDER BY `ordering`";

                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                using (IDataReader dataReader = objDb.ExecuteReader(objCmnd))
                {
                    if (dataReader.FieldCount > 0)
                    {
                        DataTable dt = new DataTable();
                        dt.Load(dataReader);
                        if (dt.Rows.Count <= 0) return null;
                        return dt;
                    }
                    else
                    {
                        return null;
                    }
                }
            }
            catch { return null; }
        }

        public static DataTable fncSearchSanPham(Database objDb, string strCatID = "", string strSearch = "", int intConHang = -1, int intKhoHang = -1)
        {
            try
            {
                strSearch = strSearch.ToLower();

                string strSQL = "";
                strSQL += " SELECT DISTINCT";
                //strSQL += " false as chon";
                strSQL += " pro.`product_id`";
                strSQL += ",pro.`name_vi-VN` as pro_name";
                strSQL += ",pro.`product_ean`";
                if (intKhoHang <= -1)
                {
                    strSQL += ",pro.`product_quantity` As `product_quantity`";
                }
                strSQL += ",pro.`product_manufacturer_id`";
                //strSQL += ",cat.`category_id`";
                //strSQL += ",cat.`name_vi-VN` as cat_name";
                //strSQL += ",CONCAT('" + System.Environment.CurrentDirectory + @"\images\'" + ",pro.`product_thumb_image`) As product_thumb_image";
                //strSQL += ",pro_cat.`product_ordering`";
                strSQL += ",gia.`gia_goc`";
                strSQL += ",gia.`gia_ban_le`";
                strSQL += ",gia.`gia_cap1`";
                strSQL += ",gia.`gia_cap2`";
                strSQL += ",gia.`gia_cap3`";
                strSQL += ",gia.`khuyen_mai`";
                if (intKhoHang > -1)
                {
                    strSQL += ",soluong.SL As `product_quantity`";
                }

                strSQL += " FROM";
                //▼▼▼▼▼▼▼▼▼▼▼▼▼▼ 2014 08 25 khong select category nua ▼▼▼▼▼
                strSQL += " `joomla_jshopping_products_to_categories` as pro_cat";

                strSQL += " Left Join `joomla_jshopping_products`  as pro";
                strSQL += " On pro_cat.product_id = pro.product_id";

                strSQL += " Left Join `joomla_jshopping_categories` as cat";
                strSQL += " On cat.category_id = pro_cat.category_id";

                strSQL += " Left Join `joomla_jshopping_giaban` as gia";
                strSQL += " On gia.product_id = pro_cat.product_id";

                //strSQL += " `joomla_jshopping_products` as pro";

                //strSQL += " Left Join `joomla_jshopping_giaban` as gia";
                //strSQL += " On gia.product_id = pro.product_id";
                //▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲
                if (intKhoHang > -1)
                {
                    strSQL += " Join (SELECT `product_id` , sum(`soluong`) as SL FROM `joomla_jshopping_soluong`";
                    strSQL += " WHERE";
                    strSQL += " kho_id = " + intKhoHang;
                    strSQL += " group by `product_id`) as soluong";
                    strSQL += " On pro.product_id = soluong.product_id";
                }

                strSQL += " WHERE";
                strSQL += " 1 = 1";

                if (intConHang > -1) strSQL += " AND pro.product_publish = " + intConHang;
                //▼▼▼▼▼▼▼▼▼▼▼▼▼▼ 2014 08 25 khong select category nua ▼▼▼▼▼
                if (strCatID != "") strSQL += " AND cat.category_id in (" + strCatID + ")";
                //▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲
                if (strSearch != "")
                {
                    strSQL += " AND (pro.`name_vi-VN` like N'%" + clsCommon.fncCnvToDbString(strSearch) + "%' Or product_ean like N'%" + clsCommon.fncCnvToDbString(strSearch) + "%'";
                    strSQL += " Or pro.`name_vi-VN` = N'" + clsCommon.fncCnvToDbString(strSearch) + "' Or product_ean = N'" + clsCommon.fncCnvToDbString(strSearch) + "')";
                }
                //▼▼▼▼▼▼▼▼▼▼▼▼▼▼ 2014 08 25 khong select category nua ▼▼▼▼▼
                //strSQL += " ORDER BY pro_cat.`product_ordering`";
                //▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲▲

                strSQL = strSQL.Replace(@"\", @"\\");

                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                using (IDataReader dataReader = objDb.ExecuteReader(objCmnd))
                {
                    if (dataReader.FieldCount > 0)
                    {
                        DataTable dt = new DataTable();
                        dt.Load(dataReader);
                        if (dt.Rows.Count <= 0) return null;
                        return dt;
                    }
                    else
                    {
                        return null;
                    }
                }
            }
            catch { return null; }
        }

        public static DataTable fncGetAttributeValue(Database objDb, clsEnum.ATTRIBUTE emAttribute, int intKhoID = 0)
        {
            try
            {
                string strSQL = "";
                strSQL += " SELECT";
                strSQL += "`value_id`";
                strSQL += ",`attr_id`";
                strSQL += ",`name_vi-VN` as `name`";
                strSQL += ",`value_ordering`";
                strSQL += ",`khohang_id`";
                strSQL += " FROM `joomla_jshopping_attr_values`";
                strSQL += " WHERE attr_id = " + (int)emAttribute;
                if (intKhoID > 0) strSQL += " AND khohang_id = " + intKhoID;
                strSQL += " ORDER BY `value_ordering`";

                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                using (IDataReader dataReader = objDb.ExecuteReader(objCmnd))
                {
                    if (dataReader.FieldCount > 0)
                    {
                        DataTable dt = new DataTable();
                        dt.Load(dataReader);
                        if (dt.Rows.Count <= 0) return null;
                        return dt;
                    }
                    else
                    {
                        return null;
                    }
                }
            }
            catch { return null; }
        }

        public static bool fncInsertAttributeValue(Database objDb, DbTransaction objTrans, int intAttr, string strName, int intKhoID, int intOrder)
        {
            try
            {
                string strSQL = "";
                strSQL += " INSERT INTO `joomla_jshopping_attr_values`";
                strSQL += " SET";
                strSQL += " `attr_id` = " + intAttr;
                strSQL += ",`value_ordering` = " + intOrder;
                strSQL += ",`name_vi-VN` = '" + clsCommon.fncCnvToDbString(strName) + "'";
                strSQL += ",`khohang_id` = " + intKhoID;

                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                int intResult = objDb.ExecuteNonQuery(objCmnd, objTrans);
                if (intResult > 0) return true;
                else return false;
            }
            catch { return false; }
        }

        public static bool fncUpdateAttributeValue(Database objDb, DbTransaction objTrans, int intValueID, string strName, int intKhoID, int intOrder)
        {
            try
            {
                string strSQL = "";
                strSQL += " UPDATE `joomla_jshopping_attr_values`";
                strSQL += " SET";
                strSQL += " `value_ordering` = " + intOrder;
                strSQL += ",`name_vi-VN` = '" + clsCommon.fncCnvToDbString(strName) + "'";
                strSQL += ",`khohang_id` = " + intKhoID;

                strSQL += " WHERE";
                strSQL += " `value_id` = " + intValueID;

                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                int intResult = objDb.ExecuteNonQuery(objCmnd, objTrans);
                if (intResult > 0) return true;
                else return false;
            }
            catch { return false; }
        }

        public static DataTable fncGetAttribute(Database objDb, int intAttrID = 0)
        {
            try
            {
                string strSQL = "";
                strSQL += " SELECT";
                strSQL += " `attr_id`";
                strSQL += ",`name_vi-VN` as `name`";
                strSQL += ",`attr_ordering`";
                strSQL += " FROM `joomla_jshopping_attr`";
                strSQL += " WHERE 1 = 1";
                if (intAttrID > 0) strSQL += " AND attr_id = " + intAttrID;
                strSQL += " ORDER BY `attr_ordering`";

                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                using (IDataReader dataReader = objDb.ExecuteReader(objCmnd))
                {
                    if (dataReader.FieldCount > 0)
                    {
                        DataTable dt = new DataTable();
                        dt.Load(dataReader);
                        if (dt.Rows.Count <= 0) return null;
                        return dt;
                    }
                    else
                    {
                        return null;
                    }
                }
            }
            catch { return null; }
        }

        public static DataTable fncGetGiaBan1(Database objDb, int intProID)
        {
            try
            {
                string strSQL = "";
                strSQL += " SELECT";
                strSQL += " `gia_goc`";
                strSQL += ",`gia_ban_le`";
                strSQL += ",`gia_cap1`";
                strSQL += ",`gia_cap2`";
                strSQL += ",`gia_cap3`";
                strSQL += ",`khuyen_mai`";
                strSQL += " FROM";
                strSQL += " `joomla_jshopping_giaban`";
                strSQL += " WHERE";
                strSQL += " `product_id` = " + intProID;

                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                using (IDataReader dataReader = objDb.ExecuteReader(objCmnd))
                {
                    if (dataReader.FieldCount > 0)
                    {
                        DataTable dt = new DataTable();
                        dt.Load(dataReader);
                        if (dt.Rows.Count <= 0) return null;
                        return dt;
                    }
                    else
                    {
                        return null;
                    }
                }
            }
            catch { return null; }
        }

        public static stGiaBan fncGetGiaBan2(Database objDb, int intProID)
        {
            stGiaBan stData = new stGiaBan();

            try
            {
                DataTable objData = fncGetGiaBan1(objDb, intProID);
                if (objData == null) return stData;

                stData.intProductID = intProID;
                stData.intGiaGoc = clsCommon.fncCnvNullToInt(objData.Rows[0]["gia_goc"]);
                stData.intGiaLe = clsCommon.fncCnvNullToInt(objData.Rows[0]["gia_ban_le"]);
                stData.intGiaCap1 = clsCommon.fncCnvNullToInt(objData.Rows[0]["gia_cap1"]);
                stData.intGiaCap2 = clsCommon.fncCnvNullToInt(objData.Rows[0]["gia_cap2"]);
                stData.intGiaCap3 = clsCommon.fncCnvNullToInt(objData.Rows[0]["gia_cap3"]);
                stData.intKhuyenMai = clsCommon.fncCnvNullToInt(objData.Rows[0]["khuyen_mai"]);

                return stData;
            }
            catch { return stData; }
        }

        public static DataTable fncGetUser(Database objDb)
        {
            try
            {
                string strSQL = "";
                strSQL += " SELECT";
                strSQL += " `id`";
                strSQL += ",`name`";
                strSQL += ",`username`";
                strSQL += ",`email`";
                strSQL += ",`phone`";
                strSQL += ",`khohang_id`";

                strSQL += " FROM";
                strSQL += " `joomla_users`";


                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                using (IDataReader dataReader = objDb.ExecuteReader(objCmnd))
                {
                    if (dataReader.FieldCount > 0)
                    {
                        DataTable dt = new DataTable();
                        dt.Load(dataReader);
                        if (dt.Rows.Count <= 0) return null;
                        return dt;
                    }
                    else
                    {
                        return null;
                    }
                }
            }
            catch { return null; }
        }

        public static DataTable fncGetRetailUser(Database objDb)
        {
            try
            {
                string strSQL = "";
                strSQL += " SELECT DISTINCT";

                strSQL += " `ten_khach`as `name`";
                strSQL += ",`sdt` as `phone`";
                strSQL += ",`email` as `email`";
                strSQL += ",'' as `username`";
                strSQL += " FROM `joomla_jshopping_donhang` ";


                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                using (IDataReader dataReader = objDb.ExecuteReader(objCmnd))
                {
                    if (dataReader.FieldCount > 0)
                    {
                        DataTable dt = new DataTable();
                        dt.Load(dataReader);
                        if (dt.Rows.Count <= 0) return null;
                        return dt;
                    }
                    else
                    {
                        return null;
                    }
                }
            }
            catch { return null; }
        }

        public static DataTable fncGetManufacturer(Database objDb, int intManuID = 0)
        {
            try
            {
                string strSQL = "";
                strSQL += " SELECT";
                strSQL += " `manufacturer_id`";
                strSQL += ",`name_vi-VN` as `name`";
                strSQL += ",`ordering`";
                strSQL += " FROM `joomla_jshopping_manufacturers`";
                strSQL += " WHERE 1 = 1";
                if (intManuID > 0) strSQL += " AND manufacturer_id = " + intManuID;
                strSQL += " ORDER BY `ordering`";

                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                using (IDataReader dataReader = objDb.ExecuteReader(objCmnd))
                {
                    if (dataReader.FieldCount > 0)
                    {
                        DataTable dt = new DataTable();
                        dt.Load(dataReader);
                        if (dt.Rows.Count <= 0) return null;
                        return dt;
                    }
                    else
                    {
                        return null;
                    }
                }
            }
            catch { return null; }
        }

        public static int fncGetMaxUser(Database objDb)
        {
            try
            {
                string strSQL = "";
                strSQL += " SELECT Max(`id`) from `joomla_users`";

                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                using (IDataReader dataReader = objDb.ExecuteReader(objCmnd))
                {
                    if (dataReader.FieldCount > 0)
                    {
                        DataTable dt = new DataTable();
                        dt.Load(dataReader);
                        if (dt.Rows.Count <= 0) return 0;

                        return clsCommon.fncCnvNullToInt(dt.Rows[0][0]);
                    }
                    else
                    {
                        return 0;
                    }
                }
            }
            catch { return 0; }
        }

        public static int fncGetMaxProduct(Database objDb)
        {
            try
            {
                string strSQL = "";
                strSQL += " SELECT Max(`product_id`) from `joomla_jshopping_products`";

                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                using (IDataReader dataReader = objDb.ExecuteReader(objCmnd))
                {
                    if (dataReader.FieldCount > 0)
                    {
                        DataTable dt = new DataTable();
                        dt.Load(dataReader);
                        if (dt.Rows.Count <= 0) return 0;

                        return clsCommon.fncCnvNullToInt(dt.Rows[0][0]);
                    }
                    else
                    {
                        return 0;
                    }
                }
            }
            catch { return 0; }
        }

        public static bool fncInsertUser(Database objDb, DbTransaction objTrans, int intID, string strFullName, string strEmail, string strPhone, int intStoreId)
        {
            try
            {
                string strSQL = "";
                strSQL += " INSERT INTO `joomla_users`";
                strSQL += " SET";
                strSQL += " `id` = " + intID;
                strSQL += ",`name`= '" + clsCommon.fncCnvToDbString(strFullName) + "'";
                strSQL += ",`username` = '" + Guid.NewGuid().ToString() + "'";
                strSQL += ",`email` = '" + clsCommon.fncCnvToDbString(strEmail) + "'";
                strSQL += ",`password` = '4bf9df4ebcf3b51e27743dad5b4697aa:Gk20lrJXmmrEw6aW5qjCDgUsqikRrcTR'";
                strSQL += ",`usertype` = ' '";
                strSQL += ",`phone` = '" + clsCommon.fncCnvToDbString(strPhone) + "'";
                strSQL += ",`khohang_id` = " + intStoreId;
                strSQL += ",`block` = ' '";
                strSQL += ",`sendEmail` = ' '";
                strSQL += ",`registerDate` = Now()";
                strSQL += ",`lastvisitDate`= Now()";
                strSQL += ",`activation` = ' '";
                strSQL += ",`params` = ' '";
                strSQL += ",`lastResetTime` = ' '";
                strSQL += ",`resetCount` = ' '";

                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                int intResult = objDb.ExecuteNonQuery(objCmnd, objTrans);
                if (intResult > 0) return true;
                else return false;
            }
            catch { return false; }
        }

        public static bool fncInsertStaff(Database objDb, DbTransaction objTrans, string strName, int intKhoID)
        {
            try
            {
                string strSQL = "";
                strSQL += " INSERT INTO `joomla_jshopping_attr_values`";
                strSQL += " SET";
                strSQL += " `attr_id` = " + (int)clsEnum.ATTRIBUTE.STAFF;
                strSQL += ",`value_ordering` = 0";
                strSQL += ",`name_vi-VN` = '" + clsCommon.fncCnvToDbString(strName) + "'";
                strSQL += ",`khohang_id` = " + intKhoID;

                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                int intResult = objDb.ExecuteNonQuery(objCmnd, objTrans);
                if (intResult > 0) return true;
                else return false;
            }
            catch { return false; }
        }

        public static bool fncInsertProduct(Database objDb, DbTransaction objTrans, stProduct stData)
        {
            try
            {
                string strSQL = "";
                strSQL += " INSERT INTO `joomla_jshopping_products`";
                strSQL += " SET";
                strSQL += " `name_vi-VN` = N'" + clsCommon.fncCnvToDbString(stData.strName) + "'";
                strSQL += ",`product_ean` = N'" + clsCommon.fncCnvToDbString(stData.strEAN) + "'";
                strSQL += ",`product_quantity` = " + stData.intSo_luong;
                strSQL += ",`product_manufacturer_id` = " + stData.intNhaSX;
                strSQL += ",`product_publish` = 1";
                strSQL += ",`product_date_added` = NOW()";
                strSQL += ",`date_modify` = NOW()";

                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                int intResult = objDb.ExecuteNonQuery(objCmnd, objTrans);
                if (intResult > 0) return true;
                else return false;
            }
            catch { return false; }
        }

        public static bool fncDeleteProduct_Category(Database objDb, DbTransaction objTrans, int intProID, int intCatID = 0)
        {
            try
            {
                string strSQL = "";
                strSQL += " DELETE FROM `joomla_jshopping_products_to_categories`";
                strSQL += " WHERE";
                strSQL += " 1 = 1";
                strSQL += " AND `product_id` = " + intProID;
                if (intCatID > 0) strSQL += " AND `category_id` = " + intCatID;

                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                int intResult = objDb.ExecuteNonQuery(objCmnd, objTrans);
                if (intResult > 0) return true;
                else return false;
            }
            catch { return false; }
        }

        public static bool fncInsertProduct_Category(Database objDb, DbTransaction objTrans, int intProID, int intCatID)
        {
            try
            {
                string strSQL = "";
                strSQL += " INSERT INTO `joomla_jshopping_products_to_categories`";
                strSQL += " SET";
                strSQL += " `category_id` = " + intCatID;
                strSQL += ",`product_id` = " + intProID;

                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                int intResult = objDb.ExecuteNonQuery(objCmnd, objTrans);
                if (intResult > 0) return true;
                else return false;
            }
            catch { return false; }
        }

        public static bool fncInsertGiaBan(Database objDb, DbTransaction objTrans, int intProID, int intGiaGoc, int intGiaLe, int intGiaCap1, int intGiaCap2, int intGiaCap3)
        {
            try
            {
                string strSQL = "";
                strSQL += " INSERT INTO";
                strSQL += " `joomla_jshopping_giaban`";
                strSQL += " SET";
                strSQL += " `gia_goc`= " + intGiaGoc;
                strSQL += ",`gia_ban_le`= " + intGiaLe;
                strSQL += ",`gia_cap1`= " + intGiaCap1;
                strSQL += ",`gia_cap2`= " + intGiaCap2;
                strSQL += ",`gia_cap3`= " + intGiaCap3;
                if (intProID > 0)
                {
                    strSQL += ",`product_id` = '" + intProID + "'";
                }
                else
                {
                    strSQL += ",`product_id` = (Select Max(product_id) From joomla_jshopping_products)";
                }
                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                int intResult = objDb.ExecuteNonQuery(objCmnd, objTrans);
                if (intResult > 0) return true;
                else return false;
            }
            catch { return false; }
        }

        public static bool fncUpdateUserStore(Database objDb, DbTransaction objTrans, int intUserID, int intStoreID)
        {
            try
            {
                string strSQL = "";
                strSQL += " UPDATE";
                strSQL += " `joomla_users`";
                strSQL += " SET `khohang_id` = 0";
                strSQL += " WHERE `khohang_id` = '" + intStoreID + "'";

                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                int intResult = objDb.ExecuteNonQuery(objCmnd, objTrans);

                strSQL = "";
                strSQL += " UPDATE";
                strSQL += " `joomla_users`";
                strSQL += " SET `khohang_id` = " + intStoreID;
                strSQL += " WHERE `id` = '" + intUserID + "'";

                objCmnd = objDb.GetSqlStringCommand(strSQL);
                intResult = objDb.ExecuteNonQuery(objCmnd, objTrans);

                if (intResult > 0) return true;
                else return false;
            }
            catch { return false; }
        }

        //public static bool fncBeginTrans(Database objDb)
        //{
        //    try
        //    {
        //        string strSQL = "";
        //        strSQL += "SET autocommit = 0;";
        //        strSQL += "START TRANSACTION;";

        //        System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
        //        int intResult = objDb.ExecuteNonQuery(objCmnd, objTrans);

        //        objCmnd = objDb.GetSqlStringCommand(strSQL);
        //        intResult = objDb.ExecuteNonQuery(objCmnd, objTrans);

        //        if (intResult > 0) return true;
        //        else return false;
        //    }
        //    catch { return false; }
        //}

        //public static bool fncCommit(Database objDb)
        //{
        //    try
        //    {
        //        string strSQL = "";                
        //        strSQL += "COMMIT;";
        //        strSQL += "SET autocommit = 1;";

        //        System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
        //        int intResult = objDb.ExecuteNonQuery(objCmnd, objTrans);

        //        objCmnd = objDb.GetSqlStringCommand(strSQL);
        //        intResult = objDb.ExecuteNonQuery(objCmnd, objTrans);

        //        if (intResult > 0) return true;
        //        else return false;
        //    }
        //    catch { return false; }
        //}

        //public static bool fncRollback(Database objDb)
        //{
        //    try
        //    {
        //        string strSQL = "";
        //        strSQL += "ROLLBACK;";
        //        strSQL += "SET autocommit = 1;";

        //        System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
        //        int intResult = objDb.ExecuteNonQuery(objCmnd, objTrans);

        //        objCmnd = objDb.GetSqlStringCommand(strSQL);
        //        intResult = objDb.ExecuteNonQuery(objCmnd, objTrans);

        //        if (intResult > 0) return true;
        //        else return false;
        //    }
        //    catch { return false; }
        //}

        public static DataTable fncGetQuantity(Database objDb, int intProductID)
        {
            try
            {
                string strSQL = "";
                strSQL += " SELECT Distinct";
                strSQL += " `product_id`";
                strSQL += ",`kho_id`";
                strSQL += ",khohang.name as `tenkho`";
                strSQL += ",khohang.value_ordering as `orderkho`";

                strSQL += ",`mau_id`";
                strSQL += ",mausac.name as `tenmau`";
                strSQL += ",mausac.value_ordering as `ordermau`";

                strSQL += ",`size_id`";
                strSQL += ",kichthuoc.name as `tensize`";
                strSQL += ",kichthuoc.value_ordering as `ordersize`";
                strSQL += ",`soluong`";
                strSQL += ",`khoiluong`";


                strSQL += " FROM";

                strSQL += " `joomla_jshopping_soluong`";

                strSQL += " Left Join (";
                strSQL += " SELECT `value_id`, `attr_id`, `name_vi-VN` as `name`, `value_ordering`";
                strSQL += " FROM `joomla_jshopping_attr_values`";
                strSQL += " ORDER BY `value_ordering` ) as khohang on khohang.value_id = joomla_jshopping_soluong.kho_id";

                strSQL += " Left Join (";
                strSQL += " SELECT `value_id`, `attr_id`, `name_vi-VN` as `name`, `value_ordering`";
                strSQL += " FROM `joomla_jshopping_attr_values`";
                strSQL += " ORDER BY `value_ordering` ) as mausac on mausac.value_id = joomla_jshopping_soluong.mau_id";

                strSQL += " Left Join (";
                strSQL += " SELECT `value_id`, `attr_id`, `name_vi-VN` as `name`, `value_ordering`";
                strSQL += " FROM `joomla_jshopping_attr_values`";
                strSQL += " ORDER BY `value_ordering` ) as kichthuoc on kichthuoc.value_id = joomla_jshopping_soluong.size_id";

                strSQL += " WHERE";
                strSQL += " product_id = " + intProductID;
                //if(intval($kho_id) >= 0) strSQL += " and kho_id = ".$kho_id;
                //if(intval($mau_id) >= 0) strSQL += " and mau_id = ".$mau_id;
                //if(intval($size_id) >= 0) strSQL += " and size_id = ".$size_id;

                strSQL += " ORDER BY orderkho";
                strSQL += ",ordermau";
                strSQL += ",ordersize";


                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                using (IDataReader dataReader = objDb.ExecuteReader(objCmnd))
                {
                    if (dataReader.FieldCount > 0)
                    {
                        DataTable dt = new DataTable();
                        dt.Load(dataReader);
                        //if (dt.Rows.Count <= 0) return null;
                        return dt;
                    }
                    else
                    {
                        return null;
                    }
                }
            }
            catch { return null; }
        }

        public static DataTable fncGetProductToCategory1(Database objDb, int intProductID)
        {
            try
            {
                string strSQL = "";
                strSQL += " SELECT";
                strSQL += " `product_id`";
                strSQL += ",`category_id`";
                strSQL += ",`product_ordering`";

                strSQL += " FROM";
                strSQL += " `joomla_jshopping_products_to_categories`";
                strSQL += " WHERE";
                strSQL += " `product_id` = " + intProductID;


                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                using (IDataReader dataReader = objDb.ExecuteReader(objCmnd))
                {
                    if (dataReader.FieldCount > 0)
                    {
                        DataTable dt = new DataTable();
                        dt.Load(dataReader);
                        if (dt.Rows.Count <= 0) return null;
                        return dt;
                    }
                    else
                    {
                        return null;
                    }
                }
            }
            catch { return null; }
        }

        public static List<int> fncGetProductToCategory2(Database objDb, int intProductID)
        {
            List<int> lstCateID = new List<int>();
            try
            {
                DataTable objData = fncGetProductToCategory1(objDb, intProductID);
                if (objData == null) return lstCateID;

                for (int i = 0; i <= objData.Rows.Count - 1; i++)
                {
                    lstCateID.Add(clsCommon.fncCnvNullToInt(objData.Rows[i]["category_id"]));
                }

                return lstCateID;
            }
            catch { return lstCateID; }
        }

        public static bool fncIsDuplicateEAN(Database objDb, string strEAN)
        {
            try
            {
                string strSQL = "";
                strSQL += " SELECT";
                strSQL += " `product_id`";
                strSQL += ",`product_ean`";
                strSQL += " FROM";
                strSQL += " `joomla_jshopping_products`";
                strSQL += " WHERE";
                strSQL += " `product_ean` = '" + clsCommon.fncCnvToDbString(strEAN) + "'";

                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                using (IDataReader dataReader = objDb.ExecuteReader(objCmnd))
                {
                    if (dataReader.FieldCount > 0)
                    {
                        DataTable dt = new DataTable();
                        dt.Load(dataReader);
                        if (dt.Rows.Count <= 0) return false;
                        else return true;
                    }
                    else
                    {
                        return false;
                    }
                }
            }
            catch { return true; }
        }

        public static int fncGetManufacturerByProduct(Database objDb, int intProID)
        {
            try
            {
                string strSQL = "";
                strSQL += " SELECT product_manufacturer_id from `joomla_jshopping_products` where product_id = " + intProID;

                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                using (IDataReader dataReader = objDb.ExecuteReader(objCmnd))
                {
                    if (dataReader.FieldCount > 0)
                    {
                        DataTable dt = new DataTable();
                        dt.Load(dataReader);
                        if (dt.Rows.Count <= 0) return 0;

                        return clsCommon.fncCnvNullToInt(dt.Rows[0][0]);
                    }
                    else
                    {
                        return 0;
                    }
                }
            }
            catch { return 0; }
        }

        public static stProduct fncGetProductDetail(Database objDb, int intProID)
        {
            stProduct stData = new stProduct();
            try
            {
                string strSQL = "";
                strSQL += " SELECT";
                strSQL += " `name_vi-VN` as pro_name";
                strSQL += ",`product_ean`";
                strSQL += ",`description_vi-VN` as pro_description ";
                strSQL += ",`product_quantity`";
                strSQL += ",`product_manufacturer_id`";

                strSQL += " from ";
                strSQL += " `joomla_jshopping_products`";

                strSQL += " where ";
                strSQL += " product_id = " + intProID;

                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                using (IDataReader dataReader = objDb.ExecuteReader(objCmnd))
                {
                    if (dataReader.FieldCount > 0)
                    {
                        DataTable dt = new DataTable();
                        dt.Load(dataReader);
                        if (dt.Rows.Count <= 0) return stData;

                        stData.strName = clsCommon.fncCnvNullToString(dt.Rows[0]["pro_name"]);
                        stData.strEAN = clsCommon.fncCnvNullToString(dt.Rows[0]["product_ean"]);
                        stData.strDescription = clsCommon.fncCnvNullToString(dt.Rows[0]["pro_description"]);
                        stData.intSo_luong = clsCommon.fncCnvNullToInt(dt.Rows[0]["product_quantity"]);
                        stData.intNhaSX = clsCommon.fncCnvNullToInt(dt.Rows[0]["product_manufacturer_id"]);
                    }
                }
                return stData;
            }
            catch { return stData; }
        }

        public static bool fncUpdateManufacturerByProduct(Database objDb, DbTransaction objTrans, stProduct stPro)
        {
            try
            {
                string strSQL = "";
                strSQL += " UPDATE `joomla_jshopping_products`";
                strSQL += " SET";
                strSQL += " `product_manufacturer_id` = " + stPro.intNhaSX;
                strSQL += ",`product_ean` = '" + clsCommon.fncCnvToDbString(stPro.strEAN) + "'";
                strSQL += ",`name_vi-VN` = '" + clsCommon.fncCnvToDbString(stPro.strName) + "'";
                strSQL += ",`description_vi-VN` = '" + clsCommon.fncCnvToDbString(stPro.strDescription) + "'";
                strSQL += ",`date_modify` = NOW()";

                strSQL += " WHERE";
                strSQL += " `product_id` = " + stPro.intProID;

                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                int intResult = objDb.ExecuteNonQuery(objCmnd, objTrans);
                if (intResult > 0) return true;
                else return false;
            }
            catch { return false; }
        }

        public static bool fncFixErrorLastProduct(Database objDb, DbTransaction objTrans, int intProID)
        {
            try
            {
                string strSQL = "";
                strSQL += " UPDATE `joomla_jshopping_products`";
                strSQL += " SET";
                strSQL += " `product_date_added` = NOW()";
                strSQL += ",`date_modify` = NOW()";

                strSQL += " WHERE";
                strSQL += " `product_id` = " + intProID;

                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                int intResult = objDb.ExecuteNonQuery(objCmnd, objTrans);
                if (intResult > 0) return true;
                else return false;
            }
            catch { return false; }
        }

        public static bool fncDeleteProduct(Database objDb, DbTransaction objTrans, int intProID)
        {
            try
            {
                string strSQL = "";

                strSQL += " DELETE FROM `joomla_jshopping_soluong` WHERE `product_id` = " + intProID + ";";
                strSQL += " DELETE FROM `joomla_jshopping_products` WHERE `product_id` = " + intProID + ";";

                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                int intResult = objDb.ExecuteNonQuery(objCmnd, objTrans);
                if (intResult > 0) return true;
                else return false;
            }
            catch { return false; }
        }

        public static bool fncEnableProduct(Database objDb, DbTransaction objTrans, int intProID)
        {
            try
            {
                string strSQL = "";

                strSQL += " UPDATE `joomla_jshopping_products` SET `product_publish` = " + 1 + " WHERE `product_id` = " + intProID;

                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                int intResult = objDb.ExecuteNonQuery(objCmnd, objTrans);
                if (intResult > 0) return true;
                else return false;
            }
            catch { return false; }
        }

        public static bool fncDisableProduct(Database objDb, DbTransaction objTrans, int intProID)
        {
            try
            {
                string strSQL = "";

                strSQL += " UPDATE `joomla_jshopping_products` SET `product_publish` = " + 0 + " WHERE `product_id` = " + intProID;

                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                int intResult = objDb.ExecuteNonQuery(objCmnd, objTrans);
                if (intResult > 0) return true;
                else return false;
            }
            catch { return false; }
        }

        public static bool fncGetProductPublishStatus(Database objDb, int intProID)
        {
            try
            {
                string strSQL = "";
                strSQL += " SELECT `product_publish` from `joomla_jshopping_products` WHERE `product_id` = " + intProID;

                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                using (IDataReader dataReader = objDb.ExecuteReader(objCmnd))
                {
                    if (dataReader.FieldCount > 0)
                    {
                        DataTable dt = new DataTable();
                        dt.Load(dataReader);
                        if (dt.Rows.Count <= 0) return false;

                        return clsCommon.fncCnvNullToBool(dt.Rows[0][0]);
                    }
                    else
                    {
                        return false;
                    }
                }
            }
            catch { return false; }
        }

        public static DataTable fncBaoCaoSoLuong(Database objDb, string strCatID = "", string strSearch = "", int intConHang = -1, int intKhoHang = -1)
        {
            try
            {
                strSearch = strSearch.ToLower();

                string strSQL = "";

                strSQL += " SELECT DISTINCT";
                strSQL += " pro.`product_id`";
                strSQL += ",pro.`name_vi-VN` as pro_name";
                strSQL += ",pro.`product_ean`";
                if (intKhoHang <= -1)
                {
                    strSQL += ",pro.`product_quantity` As `product_quantity`";
                }
                //strSQL += ",pro.`product_manufacturer_id`";
                //strSQL += ",gia.`gia_goc`";
                //strSQL += ",gia.`gia_ban_le`";
                //strSQL += ",gia.`gia_cap1`";
                //strSQL += ",gia.`gia_cap2`";
                //strSQL += ",gia.`gia_cap3`";
                //strSQL += ",gia.`khuyen_mai`";
                strSQL += ",SL.ten_kho";
                strSQL += ",SL.ten_mau";
                strSQL += ",SL.ten_size";
                strSQL += ",SL.soluong";

                if (intKhoHang > -1)
                {
                    strSQL += ",soluong.SL As `product_quantity`";
                }

                strSQL += " FROM";
                strSQL += " `joomla_jshopping_products_to_categories` as pro_cat";

                strSQL += " Left Join `joomla_jshopping_products`  as pro";
                strSQL += " On pro_cat.product_id = pro.product_id";

                strSQL += " Left Join `joomla_jshopping_categories` as cat";
                strSQL += " On cat.category_id = pro_cat.category_id";

                strSQL += " Left Join `joomla_jshopping_giaban` as gia";
                strSQL += " On gia.product_id = pro_cat.product_id";

                if (intKhoHang > -1)
                {
                    strSQL += " Join (SELECT `product_id` , sum(`soluong`) as SL FROM `joomla_jshopping_soluong`";
                    strSQL += " WHERE";
                    strSQL += " kho_id = " + intKhoHang;
                    strSQL += " group by `product_id`) as soluong";
                    strSQL += " On pro.product_id = soluong.product_id";
                }

                strSQL += " LEFT JOIN";
                strSQL += "(";
                strSQL += " SELECT `product_id`, `kho_id`, `mau_id`, `size_id`, `soluong`, `khoiluong` ";
                strSQL += ",kho.`name_vi-VN` as ten_kho";
                strSQL += ",mau.`name_vi-VN` as ten_mau";
                strSQL += ",size.`name_vi-VN` as ten_size";
                strSQL += " FROM `joomla_jshopping_soluong`";
                strSQL += " Left Join `joomla_jshopping_attr_values` kho on kho.value_id = kho_id";
                strSQL += " Left Join `joomla_jshopping_attr_values` mau on mau.value_id = mau_id";
                strSQL += " Left Join `joomla_jshopping_attr_values` size on size.value_id = size_id";
                strSQL += ") As SL on SL.`product_id` =  pro.`product_id`";

                strSQL += " WHERE";
                strSQL += " 1 = 1";

                if (intConHang > -1) strSQL += " AND pro.product_publish = " + intConHang;
                if (strCatID != "") strSQL += " AND cat.category_id in (" + strCatID + ")";
                if (strSearch != "")
                {
                    strSQL += " AND (pro.`name_vi-VN` like N'%" + clsCommon.fncCnvToDbString(strSearch) + "%' Or product_ean like N'%" + clsCommon.fncCnvToDbString(strSearch) + "%'";
                    strSQL += " Or pro.`name_vi-VN` = N'" + clsCommon.fncCnvToDbString(strSearch) + "' Or product_ean = N'" + clsCommon.fncCnvToDbString(strSearch) + "')";
                }

                strSQL = strSQL.Replace(@"\", @"\\");

                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                using (IDataReader dataReader = objDb.ExecuteReader(objCmnd))
                {
                    if (dataReader.FieldCount > 0)
                    {
                        DataTable dt = new DataTable();
                        dt.Load(dataReader);
                        if (dt.Rows.Count <= 0) return null;
                        return dt;
                    }
                    else
                    {
                        return null;
                    }
                }
            }
            catch { return null; }
        }

        public static DataTable fncGetCustomerTotalOrderValue(Database objDb)
        {
            try
            {
                string strSQL = "";
                strSQL += " SELECT";
                strSQL += " `ten_khach`";
                strSQL += ",`sdt`";
                strSQL += ",`email`";
                strSQL += ",`tong_cong`";
                strSQL += " FROM ";
                strSQL += " `joomla_jshopping_donhang`";


                strSQL += " GROUP BY";
                strSQL += " `ten_khach`";
                strSQL += ",`sdt`";
                strSQL += ",`email`";


                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                using (IDataReader dataReader = objDb.ExecuteReader(objCmnd))
                {
                    if (dataReader.FieldCount > 0)
                    {
                        DataTable dt = new DataTable();
                        dt.Load(dataReader);
                        if (dt.Rows.Count <= 0) return null;
                        return dt;
                    }
                    else
                    {
                        return null;
                    }
                }
            }
            catch { return null; }
        }

        public static DataTable fncGetCustomerOrders(Database objDb, string strCustomerName, string strCustomerEmail, string strCustomerPhone)
        {
            try
            {
                string strSQL = "";
                strSQL += " SELECT";
                strSQL += " `don_hang_id`";
                strSQL += ",`kho_hang_id`";
                strSQL += ",`loai_khach`";
                strSQL += ",`ten_khach`";
                strSQL += ",`sdt`";
                strSQL += ",`email`";
                strSQL += ",`nguoi_ban1`";
                strSQL += ",`nguoi_ban2`";
                strSQL += ",`van_chuyen`";
                strSQL += ",`tong_cong`";
                strSQL += ",`ghi_chu`";
                strSQL += ",`ngay_ban`";
                strSQL += " FROM";
                strSQL += " `joomla_jshopping_donhang`";
                strSQL += " WHERE";
                strSQL += " 1 = 1";
                strSQL += " AND `ten_khach` = '"+ clsCommon.fncCnvToDbString(strCustomerName) +"'";
                strSQL += " AND `sdt` = '" + clsCommon.fncCnvToDbString(strCustomerPhone) + "'";
                strSQL += " AND `email` = '" + clsCommon.fncCnvToDbString(strCustomerEmail) + "'";


                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                using (IDataReader dataReader = objDb.ExecuteReader(objCmnd))
                {
                    if (dataReader.FieldCount > 0)
                    {
                        DataTable dt = new DataTable();
                        dt.Load(dataReader);
                        if (dt.Rows.Count <= 0) return null;
                        return dt;
                    }
                    else
                    {
                        return null;
                    }
                }
            }
            catch { return null; }
        }

        #endregion

        //public static DataTable fncGetDanhSachLopNghe(Database objDb, int intLopID)
        //{
        //    try
        //    {
        //        object[] parameters = new object[]
        //        {
        //            Null.GetNull(intLopID, DBNull.Value)
        //        };

        //        using (IDataReader dataReader = objDb.ExecuteReader("usrProc_GetDanhSachLopNghe", parameters))
        //        {
        //            if (dataReader.FieldCount > 0)
        //            {
        //                DataTable dt = new DataTable();
        //                dt.Load(dataReader);
        //                return dt;
        //            }
        //            else
        //            {
        //                return null;
        //            }
        //        }
        //    }
        //    catch { return null; }
        //}


    }
}